import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection
import os
from contextlib import contextmanager
import time
import jieba
from tqdm import tqdm

MYSQL_NAME='数据库名'
MYSQL_USER = '用户名'
MYSQL_HOST = "数据库地址"
MYSQL_PW = "密码"
MYSQL_PORT = 3306
pool = PooledDB(creator=pymysql,maxconnections=6,mincached=2,maxcached=5,maxshared=3,blocking=True,maxusage=None,setsession=[],ping=1,host=MYSQL_HOST,port=MYSQL_PORT,user=MYSQL_USER,password=MYSQL_PW,
            database=MYSQL_NAME,
            charset='utf8mb4'
        )

@contextmanager
def get_cursor():
    conn = pool.connection()
    # 默认就自动提交
    conn.autocommit = True
    try:
        cur=conn.cursor()
        yield cur
        # 或设置手动提交 con.commit()
    finally:
        # 用完放回连接池
        conn.commit()
        cur.close()
        conn.close()

#把时间戳转化为时间
def TimeStampToTime(timestamp):
    timeStruct = time.localtime(timestamp)
    return time.strftime('%Y-%m-%d %H:%M:%S',timeStruct)

#获取文件的大小,结果保留两位小数，单位为MB
def get_FileSize(filePath):
    fsize = os.path.getsize(filePath)
    fsize = fsize/float(1024*1024)
    return round(fsize,2)

#获取文件的创建时间
def get_FileCreateTime(filePath):
    t = os.path.getctime(filePath)
    return TimeStampToTime(t)

base_path=os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
for img_file in ['static/gril_image','static/manhua']:
    img_dir=os.path.join(base_path,img_file)
    for dir in tqdm(os.listdir(img_dir)):
        dir_abspath=os.path.join(img_dir,dir)
        dir_path=os.path.join(img_file,dir)
        for file in os.listdir(dir_abspath):
            # 文件信息提取
            file_abspath = os.path.join(dir_abspath, file)
            file_path = os.path.join(dir_path, file)
            file_createTime=get_FileCreateTime(file_abspath)
            file_size=get_FileSize(file_abspath)
            file_dirname=dir
            file_name=file
            type_=img_file.split('/')[-1]
            # 数据存储
            # with get_cursor() as cur:
            #     # 路径是否存在
            #     cur.execute('select count(*) from img_table where abspath=%s', (file_path,))
            #     res = cur.fetchone()
            #     if not res[0]:
            #         cur.execute('insert into img_table value (NULL,%s,%s,%s,%s,%s,%s,%s)',
            #                     (file_name, file_dirname, file_path,file_abspath, file_size, file_createTime, type))
            #判断日期是否为今天
            file_timetamp=os.path.getctime(file_abspath)
            today_timetamp=time.mktime(time.strptime(time.strftime('%Y-%m-%d 0:0:0',time.localtime()),'%Y-%m-%d %H:%M:%S'))
            if file_timetamp>today_timetamp:
                # 数据存储
                with get_cursor() as cur:
                    # 路径是否存在
                    cur.execute('select count(*) from img_table where abspath=%s',(file_path,))
                    res=cur.fetchone()
                    if not res[0]:
                        fenci='/'.join(jieba.cut(file_dirname))
                        cur.execute('insert into img_table value (NULL,%s,%s,%s,%s,%s,%s,%s,%s)',(file_name, file_dirname, file_path,file_abspath, file_size, file_createTime, type_,fenci))
